package com.gpipe.dbwriter;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.*;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;

import com.gpipe.dirwatcherRealtime.Conf;
import com.gpipe.tools.GpipeInit;
import com.gpipe.tools.SignalList;


public class Mysql {

    protected String dbtns;
    protected String dbclass;
    protected String tableName;
    protected PreparedStatement prepStatement;
    protected boolean batchExecuted;
    protected AtomicInteger counter;
    protected int batchSize = 50000;

    public Connection createConnect() {
        String dbtns = "jdbc:mysql://localhost:3306/signaldb?characterEncoding=utf-8&amp;noAccessToProcedureBodies=true&amp;rewriteBatchedStatements=true&amp;useUnicode=true";
        String dbclass = "com.mysql.jdbc.Driver";//"org.gjt.mm.mysql.Driver";
        String user = "signal";
        String pass = "sig123!@#NAL";
        Connection conn =commanCreateConnect(dbtns,dbclass,user,pass);
        return conn;
    }
    public Connection tmpConnect() {
        String dbtns = "jdbc:mysql://localhost:3306/signaldb?characterEncoding=utf-8&amp;noAccessToProcedureBodies=true&amp;rewriteBatchedStatements=true&amp;useUnicode=true";
        String dbclass = "com.mysql.jdbc.Driver";//"org.gjt.mm.mysql.Driver";
        String user = "root";
        String pass = "123567";
        Connection conn =commanCreateConnect(dbtns,dbclass,user,pass);
        return conn;
    }

    public Connection commanCreateConnect(String dbtns,String dbclass,String user,String pass){
        Connection conn = null;
        this.batchSize= Conf.batchSize;
        GpipeInit.writeLogs("--info--:batchSize="+this.batchSize);
        counter = new AtomicInteger(0);
        this.dbtns = dbtns;
        this.dbclass = dbclass;
        try {
            Class.forName(dbclass);
            conn = DriverManager.getConnection(dbtns, user, pass);
//            conn.setAutoCommit(false);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }

        return conn;
    }

    public int deleteMysql(String sql, Connection connection) {
        if (connection == null) {
            connection = createConnect();
        }
        if (connection == null) {
            GpipeInit.writeLogs("--error-:can not connect to mysql, please check status of mysql service.");
            System.exit(0);
        }
        PreparedStatement statement = null;
        try {
            String ST = GpipeInit.writeLogs("--delete sql:" + sql);
            statement = connection.prepareStatement(sql);
            int i = statement.executeUpdate();


            String ET = GpipeInit.writeLogs("--finish delete sql:" );
            GpipeInit.writeLogs("--info--:delete sql total time :" + GpipeInit.duration(ST, ET, "yyyy-MM-dd HH:mm:ss") + "s");

            return i;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    public int updateMysql(String sql, Connection connection) {
        if (connection == null) {
            connection = createConnect();
        }
        if (connection == null) {
            GpipeInit.writeLogs("--error-:can not connect to mysql, please check status of mysql service.");
            System.exit(0);
        }
        PreparedStatement statement = null;
        try {
            String ST = GpipeInit.writeLogs("--update sql:" + sql);
            statement = connection.prepareStatement(sql);
            int i = statement.executeUpdate();


            String ET = GpipeInit.writeLogs("--finish update sql:" );
            GpipeInit.writeLogs("--info--:update sql total time :" + GpipeInit.duration(ST, ET, "yyyy-MM-dd HH:mm:ss") + "s");

            return i;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }





    public ResultSet readMysql(String selectSql, Connection connection) {
        if (connection == null) {
            connection = createConnect();
        }
        if (connection == null) {
            GpipeInit.writeLogs("--error-:can not connect to mysql, please check status of mysql service.");
            System.exit(0);
        }
//		selectSql = "select * from testpass";//SQL语句
        PreparedStatement statement = null;
        try {

            statement = connection.prepareStatement(selectSql);
            ResultSet rs = statement.executeQuery();
            rs.last();
            GpipeInit.writeLogs("--info--:readMysql="+selectSql+"--rows:"+rs.getRow());
            rs.beforeFirst();
            return rs;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public AtomicInteger writeMysql(List<String[]> in, String sql, Connection connection) {
        if (connection == null) {
            connection = createConnect();
        }
        if (connection == null) {
            GpipeInit.writeLogs("--error-:can not connect to mysql, please check status of mysql service.");
            System.exit(0);
        }
        String ST = GpipeInit.writeLogs("begin insert rows：" + sql);
        StringBuilder insertQuery = new StringBuilder(sql);
        PreparedStatement prepStatement = null;
        AtomicInteger counter = new AtomicInteger(0);
        AtomicInteger all = new AtomicInteger(0);
        try {
            connection.setAutoCommit(false);
            prepStatement = connection.prepareStatement(insertQuery.toString());

            for (String[] row : in) {
                for (int i = 0; i < row.length; i++) {
                    prepStatement.setString(i + 1, row[i]);
//                    prepStatement.setString(i + 1, formatting(row[i]));
//                    prepStatement.setBytes(i+1,row[i].getBytes());
                }
                prepStatement.addBatch();
                counter.incrementAndGet();

                if (counter.get() == batchSize) {
                    prepStatement.executeBatch();
                    connection.commit();
                    prepStatement.clearBatch();
                    all.addAndGet(counter.intValue());
                    counter = new AtomicInteger(0);
                }
            }

            if (counter.get() > 0) {
                prepStatement.executeBatch();
                connection.commit();
                prepStatement.clearBatch();
                all.addAndGet(counter.intValue());
                counter = new AtomicInteger(0);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        String ET = GpipeInit.writeLogs("insert rows：" + all.intValue() + "|" + sql);
        GpipeInit.writeLogs("--info--:insert sql total time :" + GpipeInit.duration(ST, ET, "yyyy-MM-dd HH:mm:ss") + "s");

        return all;
    }
    private String formatting(String str){
        try {
            byte[] bytes = str.getBytes("utf-8");
            return new String(bytes);
        } catch (Exception ex) {
            return "";
        }
    }

    public void writeMysql(Map<String, List<SignalList>> in, Connection connection) {
        if (connection == null) {
            connection = createConnect();
        }
        if (connection == null) {
            GpipeInit.writeLogs("--error-:can not connect to mysql, please check status of mysql service.");
            System.exit(0);
        }
        StringBuilder insertQuery = new StringBuilder("INSERT INTO jw2_list_nbr_group_bts_sum(date,nbr,latn,lac,ci,startTime,endTime)VALUES(?,?,?,?,?,?,?)");
        PreparedStatement prepStatement = null;
        AtomicInteger counter = new AtomicInteger(0);
        AtomicInteger all = new AtomicInteger(0);
        try {
            connection.setAutoCommit(false);
            prepStatement = connection.prepareStatement(insertQuery.toString());


            Iterator iter = in.entrySet().iterator();
            while (iter.hasNext()) {
                Map.Entry<String, List<SignalList>> entry = (Map.Entry) iter.next();
                List<SignalList> val = entry.getValue();
                for (SignalList sl : val) {
                    prepStatement.setString(1, sl.getDate());
                    prepStatement.setString(2, sl.getNbr());
                    prepStatement.setString(3, "-1");
                    prepStatement.setString(4, sl.getLac());
                    prepStatement.setString(5, sl.getCi());
                    prepStatement.setString(6, sl.getStartTime());
                    prepStatement.setString(7, sl.getEndTime());
                    prepStatement.addBatch();
                    counter.incrementAndGet();
                }

                if (counter.get() == batchSize) {
                    prepStatement.executeBatch();
                    connection.commit();
                    prepStatement.clearBatch();
                    all.addAndGet(counter.intValue());
                    counter = new AtomicInteger(0);
                }
            }

            if (counter.get() > 0) {
                prepStatement.executeBatch();
                connection.commit();
                prepStatement.clearBatch();
                all.addAndGet(counter.intValue());
                counter = new AtomicInteger(0);
            }
            connection.close();
            GpipeInit.writeLogs("insert rows：" + all.intValue());

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }


    public void writeMysql(String filePath, Connection connection) {
        if (connection == null) {
            connection = createConnect();
        }
        if (connection == null) {
            GpipeInit.writeLogs("--error-:can not connect to mysql, please check status of mysql service.");
            System.exit(0);
        }

        StringBuilder insertQuery = new StringBuilder("INSERT INTO signal_data_gz(MSG_TYPE,MSG_SUB_TYPE,ACC_NBR,LAC,CI,START_TIME)VALUES(?,?,?,?,?,?)");
        try {
            prepStatement = connection.prepareStatement(insertQuery.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }

        BufferedReader reader = null;
        try {
            reader = new BufferedReader(new InputStreamReader(new FileInputStream(filePath)));
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        String line = null;
        try {
            while ((line = reader.readLine()) != null) {
                if (line != null) {
                    String[] fields = null;
                    fields = line.split("\\|", -1);
                    //2,3,5,9,10,11
                    if (fields.length >= 11) {
                        try {
                            if (GpipeInit.isNotEmpty(fields[5])) {
                                prepStatement.setString(1, fields[2]);
                                prepStatement.setString(2, fields[3]);
                                prepStatement.setString(3, fields[5]);
                                prepStatement.setString(4, fields[9]);
                                prepStatement.setString(5, fields[10]);
                                prepStatement.setString(6, fields[11]);
                                prepStatement.addBatch();
                                counter.incrementAndGet();
                                if (counter.get() == batchSize) {
                                    executeBatch();
                                }
                            }
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }
                }
            }
            reader.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            if (counter.get() > 0) {
                executeBatch();
            }
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void executeBatch() throws SQLException {
        prepStatement.executeBatch();
        prepStatement.clearBatch();
        counter = new AtomicInteger(0);
    }
}
